﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Reflection;
using DocumentFormat.OpenXml.Spreadsheet;
using eCommerce.core;
using eCommerce.core.Helpers;
using eCommerce.db.BORespository;
using eCommerce.db.DataRespository;
using eCommerce.db.SQLDataAccess;

namespace eCommerce.db.DALRespository
{
    public class ProductDal
    {
        #region ProductDal private
        private static void SetInfo(IDataRecord reader, ref ProductEntity o)
        {
            for (int i = 0; i < reader.FieldCount; i++)
            {
                PropertyInfo propertyInfo = DbCommon.GetPropertyInfo(o, reader.GetName(i));
                if (((propertyInfo != null) && (reader[i] != null)) && (reader[i] != DBNull.Value))
                    propertyInfo.SetValue(o, reader[i], null);
                else
                    o[reader.GetName(i)] = reader[i];
            }
        }

        private static void SetInfoList(ref SqlDataReader reader, ref List<ProductEntity> productList)
        {
            while (reader.Read())
            {
                var o = new ProductEntity();
                SetInfo(reader, ref o);
                productList.Add(o);
            }
            reader.Dispose();
        }
        #endregion

        public static DataTable GetListByCategoryId(int categoryId)
        {          
            DbConnection.SetConnection(DbConnection.ConnectionName.Main);            
            try
            {
                return SqlHelper.ExecuteDataset(DbCommon.ConnectionString, DbCommon.DatabaseSchema + "Product_GetByCategoryId", categoryId).Tables[0];               
            }
            catch (Exception ex)
            {
                throw new Exception(string.Format(DbCommon.DatabaseSchema + "Product_GetByCategoryId: {0}", ex.Message));
            }   
        }

        public static DataTable GetListByLstProduct(int categoryId, string lstProduct)
        {
            DbConnection.SetConnection(DbConnection.ConnectionName.Main);
            try
            {
                return SqlHelper.ExecuteDataset(DbCommon.ConnectionString, DbCommon.DatabaseSchema + "Product_GetListByLstProduct", categoryId, lstProduct).Tables[0];
            }
            catch (Exception ex)
            {
                throw new Exception(string.Format(DbCommon.DatabaseSchema + "Product_GetListByLstProduct: {0}", ex.Message));
            }
        }

        public static List<ProductEntity> GetListFilter(string lstCategory, string lstPropertyValue, string orderBy, int pageIndex, int pageSize, ref int totalRecords)
        {
            var lstProduct = new List<ProductEntity>();
            DbConnection.SetConnection(DbConnection.ConnectionName.Main);
            var con = new SqlConnection(DbCommon.ConnectionString);
            try
            {
                con.Open();
                if (con.State == ConnectionState.Open)
                {
                    var cmd = new SqlCommand();
                    DbCommon.AddParameter(cmd, "CategoryId", lstCategory);
                    DbCommon.AddParameter(cmd, "PropertyValueId", lstPropertyValue);
                    DbCommon.AddParameter(cmd, "OrderBy", orderBy);
                    DbCommon.AddParameter(cmd, "PageIndex", pageIndex);
                    DbCommon.AddParameter(cmd, "PageSize", pageSize);                    

                    cmd.Connection = con;
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.CommandText = DbCommon.DatabaseSchema + "Product_Filter";

                    var reader = cmd.ExecuteReader();
                    SetInfoList(ref reader, ref lstProduct);
                    reader.Close();

                    return lstProduct;
                }                
            }
            catch (Exception ex)
            {
                throw new Exception(string.Format(DbCommon.DatabaseSchema + "Product_Filter: {0}", ex.Message));
            }

            return null;
        }

        public static List<ProductEntity> GetListLatest(int count, int status)
        {
            DbConnection.SetConnection(DbConnection.ConnectionName.Main);
            var lstProduct = new List<ProductEntity>();
            try
            {
                var reader = SqlHelper.ExecuteReader(DbCommon.ConnectionString,
                    DbCommon.DatabaseSchema + "Product_GetListLatest", count, status);
                SetInfoList(ref reader, ref lstProduct);
                return lstProduct;
            }
            catch (Exception ex)
            {
                throw new Exception(string.Format(DbCommon.DatabaseSchema + "Product_GetListLatest: {0}", ex.Message));
            }
        }

        public static List<ProductEntity> GetListLatest_Paging( int status,int pageIndex, int pageSize, ref int totalRecords)
        {
            var lstProduct = new List<ProductEntity>();
            DbConnection.SetConnection(DbConnection.ConnectionName.Main);
            var con = new SqlConnection(DbCommon.ConnectionString);
            try
            {
                con.Open();
                if (con.State == ConnectionState.Open)
                {
                    var cmd = new SqlCommand();
                    DbCommon.AddParameter(cmd, "Status", status);
                    DbCommon.AddParameter(cmd, "PageIndex", pageIndex);
                    DbCommon.AddParameter(cmd, "PageSize", pageSize);

                    cmd.Connection = con;
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.CommandText = DbCommon.DatabaseSchema + "Product_GetListLatest_Paging";

                    var reader = cmd.ExecuteReader();
                    SetInfoList(ref reader, ref lstProduct);
                    reader.Close();

                    return lstProduct;
                }
            }
            catch (Exception ex)
            {
                throw new Exception(string.Format(DbCommon.DatabaseSchema + "Product_GetListLatest_Paging: {0}", ex.Message));
            }

            return null;
        }


        public static List<ProductEntity> GetRelateProduct(int top ,int productId, int categoryId)
        {
            DbConnection.SetConnection(DbConnection.ConnectionName.Main);
            var lstProduct = new List<ProductEntity>();
            try
            {
                var reader = SqlHelper.ExecuteReader(DbCommon.ConnectionString,
                    DbCommon.DatabaseSchema + "Product_GetRelateProduct",top, productId, categoryId);
                SetInfoList(ref reader, ref lstProduct);
                return lstProduct;
            }
            catch (Exception ex)
            {
                throw new Exception(string.Format(DbCommon.DatabaseSchema + "Product_GetRelateProduct: {0}", ex.Message));
            }
        }

        public static List<ProductEntity> GetListProperty(int productId)
        {
            DbConnection.SetConnection(DbConnection.ConnectionName.Main);
            var lstProduct = new List<ProductEntity>();
            try
            {
                var reader = SqlHelper.ExecuteReader(DbCommon.ConnectionString,
                    DbCommon.DatabaseSchema + "Product_ShowPropertyDetail", productId);
                SetInfoList(ref reader, ref lstProduct);
                return lstProduct;
            }
            catch (Exception ex)
            {
                throw new Exception(string.Format(DbCommon.DatabaseSchema + "Product_GetListDiscount: {0}", ex.Message));
            }
        }

        public static List<ProductEntity> GetListDiscount(int count)
        {
            DbConnection.SetConnection(DbConnection.ConnectionName.Main);
            var lstProduct = new List<ProductEntity>();
            try
            {
                var reader = SqlHelper.ExecuteReader(DbCommon.ConnectionString,
                    DbCommon.DatabaseSchema + "Product_GetListDiscount", count);
                SetInfoList(ref reader, ref lstProduct);
                return lstProduct;
            }
            catch (Exception ex)
            {
                throw new Exception(string.Format(DbCommon.DatabaseSchema + "Product_GetListDiscount: {0}", ex.Message));
            }
        }

        public static List<ProductEntity> GetListDiscount_Paging( int pageIndex, int pageSize, ref int totalRecords)
        {
            var lstProduct = new List<ProductEntity>();
            DbConnection.SetConnection(DbConnection.ConnectionName.Main);
            var con = new SqlConnection(DbCommon.ConnectionString);
            try
            {
                con.Open();
                if (con.State == ConnectionState.Open)
                {
                    var cmd = new SqlCommand();
                    DbCommon.AddParameter(cmd, "PageIndex", pageIndex);
                    DbCommon.AddParameter(cmd, "PageSize", pageSize);

                    cmd.Connection = con;
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.CommandText = DbCommon.DatabaseSchema + "Product_GetListDiscount_Paging";

                    var reader = cmd.ExecuteReader();
                    SetInfoList(ref reader, ref lstProduct);
                    reader.Close();

                    return lstProduct;
                }
            }
            catch (Exception ex)
            {
                throw new Exception(string.Format(DbCommon.DatabaseSchema + "Product_GetListDiscount_Paging: {0}", ex.Message));
            }

            return null;
        }

        public static List<ProductEntity> Search(string keyword, int categoryId, int status, int orderBy, int pageIndex, int pageSize, ref int totalRecords)
        {
            var filter = "1=1";

            if (!string.IsNullOrEmpty(keyword))
                filter += string.Format(" AND (Code LIKE '%{0}%' OR Name LIKE '%{0}%')", keyword);

            if (categoryId > 0)
                filter += string.Format(" AND CategoryId IN (SELECT CategoryId FROM Category WHERE CategoryId = {0} OR ParentId = {0})", categoryId);                

            if (status > 0)
                filter += string.Format(" AND [Status] = {0}", status);

            #region Sort By
            string sortBy;
            switch (orderBy)
            {
                case 1:
                    sortBy = "ProductId DESC";
                    break;
                case 2:
                    sortBy = "ProductId ASC";
                    break;
                case 3:
                    sortBy = "UnitPrice ASC";
                    break;
                case 4:
                    sortBy = "UnitPrice DESC";
                    break;                    
                default:
                    sortBy = "ProductId ASC";
                    break;
            }

            #endregion

            var lstProduct = new List<ProductEntity>();

            DbConnection.SetConnection(DbConnection.ConnectionName.Main);
            var con = new SqlConnection(DbCommon.ConnectionString);
            try
            {
                con.Open();                
                var cmd = new SqlCommand();
                DbCommon.AddParameter(cmd, "datasrc", Constant.TableProduct);
                DbCommon.AddParameter(cmd, "orderBy", sortBy);
                DbCommon.AddParameter(cmd, "fieldlist", Constant.ProductFieldList);
                DbCommon.AddParameter(cmd, "filter", filter);
                DbCommon.AddParameter(cmd, "pageNum", pageIndex);
                DbCommon.AddParameter(cmd, "pageSize", pageSize);                

                cmd.Connection  = con;
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = DbCommon.DatabaseSchema + "Action";

                var reader = cmd.ExecuteReader();
                SetInfoList(ref reader, ref lstProduct);
                reader.Close();

                totalRecords = (int)SqlHelper.ExecuteScalar(DbCommon.ConnectionString, DbCommon.DatabaseSchema + "Action_Count", Constant.TableProduct, filter);

                return lstProduct;
            }
            catch (Exception ex)
            {
                throw new Exception(string.Format(DbCommon.DatabaseSchema + "Product_Action_Pagination: {0}", ex.Message));
            }
            finally
            {
                if (con.State == ConnectionState.Open)
                {
                    con.Close();
                    con.Dispose();
                }
            }                                 
        }

        public static ProductEntity GetById(int productId)
        {
            DbConnection.SetConnection(DbConnection.ConnectionName.Main);
            var product = new ProductEntity();
            try
            {
                SqlDataReader reader = SqlHelper.ExecuteReader(DbCommon.ConnectionString, "Product_GetById", productId);
                if (reader.Read())
                    SetInfo(reader, ref product);
                else product = null;
                return product;
            }
            catch (Exception ex)
            {
                throw new Exception(string.Format("{0}", ex.Message));
            }
        }
        
        public static void Save(ProductEntity o)
        {
            DbConnection.SetConnection(DbConnection.ConnectionName.Main);
            try
            {
                if (o.ProductId > 0)
                {
                    SqlHelper.ExecuteNonQuery(
                    DbCommon.ConnectionString,
                    DbCommon.DatabaseSchema + "Product_Update",
                        o.ProductId, o.CategoryId, o.Status, o.Code, o.Name, o.Url, o.Description, o.ImagePath, o.Content, o.UnitPrice, o.SpecialPrice, o.EditedBy);    
                }
                else
                {
                    SqlHelper.ExecuteNonQuery(
                    DbCommon.ConnectionString,
                    DbCommon.DatabaseSchema + "Product_Create",
                        o.CategoryId, o.Status, o.Code, o.Name, o.Url, o.Description, o.ImagePath, o.Content, o.UnitPrice, o.SpecialPrice, o.CreatedBy);                                        
                }
            }
            catch (Exception ex)
            {
                throw new Exception(string.Format(DbCommon.DatabaseSchema + "Product Save: {0}", ex.Message));
            }
        }

        public static int Create(ProductEntity o)
        {
            try
            {
                return (int) SqlHelper.ExecuteScalar(
                DbCommon.ConnectionString,
                DbCommon.DatabaseSchema + "Product_Create",
                    o.CategoryId, o.Status, o.Code, o.Name, o.Url, o.Description, o.ImagePath, o.Content, o.UnitPrice, o.SpecialPrice, o.CreatedBy);
            }
            catch (Exception ex)
            {
                throw new Exception(string.Format(DbCommon.DatabaseSchema + "Product_Create: {0}", ex.Message));
            }
        }
            
        public static void Delete(int id)
        {
            try
            {
                if (id > 0)
                    SqlHelper.ExecuteNonQuery(DbCommon.ConnectionString, DbCommon.DatabaseSchema + "Product_Delete", id);
            }
            catch (Exception ex)
            {
                throw new Exception(string.Format(DbCommon.DatabaseSchema + "Category Delete: {0}", ex.Message));
            }
        }

        public static void UpdateStatus(int id, int status)
        {         
            SqlHelper.ExecuteNonQuery(DbCommon.ConnectionString, DbCommon.DatabaseSchema + "Product_Update_Status", id, status);
        }

        public static void UpdateImagePath(int id, string imgPath)
        {
            SqlHelper.ExecuteNonQuery(DbCommon.ConnectionString, DbCommon.DatabaseSchema + "Product_Update_ImagePath", id, imgPath);
        }

        public static int GetStatistic(int status)
        {
            return (int) SqlHelper.ExecuteScalar(DbCommon.ConnectionString, DbCommon.DatabaseSchema + "Product_GetStatistic", status);
        }

        #region Read Excel

        public static List<ProductEntity> GetDataExcel(int categoryId, int status, int propertyType, Worksheet worksheet, SharedStringTable sharedString, ref string errorMessage)
        {                        
            var result = new List<ProductEntity>();

            var category = CategoryBo.GetById(categoryId);
            if (!string.IsNullOrEmpty(category.Name))
            {                
                IEnumerable<Row> dataRows =
                    from row in worksheet.Descendants<Row>()
                    where row.RowIndex > 1
                    select row;

                var count = 0;
                var lstPv = PropertyValueBo.GetAllListByPropertyId(propertyType);

                var parentName = CategoryBo.GetCategoryName(category.ParentId);                            

                foreach (Row row in dataRows)
                {
                    count++;                
                    IEnumerable<string> textValues =
                        from cell in row.Descendants<Cell>()
                        where cell.CellValue != null
                        select
                            (cell.DataType != null
                             && cell.DataType.HasValue
                             && cell.DataType == CellValues.SharedString
                                 ? sharedString.ChildElements[
                                     Int32.Parse(cell.CellValue.InnerText)].InnerText
                                 : cell.CellValue.InnerText);

                    if (textValues.Count() > 0)
                    {                    
                        try
                        {
                            var textArray = textValues.ToArray();
                            var code = textArray[0];

                            var o = new ProductEntity();
                            o.LstPropertyValue = new List<PropertyValueEntity>();

                            o.CategoryId    = categoryId;
                            o.Status        = status;
                            o.Code          = code;
                            o.Name          = category.Name;
                            o.ParentName    = parentName;
                            o.Url           = string.Empty;
                            o.Description   = textArray[1];
                            o.Content       = string.Empty;
                            o.UnitPrice     = Utils.ToDecimal(textArray[2]);
                            o.SpecialPrice  = 0;
                            o.ImageCount    = Utils.ToInt32(textArray[4]);

                            for (int i = 0; i < lstPv.Count; i++)
                            {
                                var j = i + 5;
                                var pvItem = lstPv[i];
                                var pv = new PropertyValueEntity();
                                pv.PropertyValueId  = pvItem.PropertyValueId;
                                pv.PropertyId       = pvItem.PropertyId;
                                pv.Name             = pvItem.Name;
                                pv.PropertyId       = pvItem.PropertyId;

                                var isRealProperty = Utils.ToInt32(textArray[j]);
                                pv.IsSelected = isRealProperty;

                                if (isRealProperty > 0)
                                    o.LstPropertyValue.Add(pv);
                            }

                            result.Add(o);                            
                        }
                        catch(Exception ex)
                        {
                            errorMessage += string.Format("<tr><td colspan=\"6\">Lỗi dòng: {0} - {1}</td></tr>", count, ex.Message);
                        }
                    }
                    else
                        break;
                }    
            }            

            return result;
        }        
        #endregion
    }
}
